import time
import requests
import json
import math
from openpyxl import load_workbook



etime = int(time.time())   # 结束时间
# stime = etime - 7*24*3600  # 开始时间
headers = {"X-User-Token":"756283292c03bd29dbf8a721f5ca26bc", "content-type":"application/json"}


def avg(list_s):
    ret = 0
    times = 0
    for i in list_s:
        ret += i
        times += 1
    ret = ret/times
    return ret

def variance(list_s):
    ret = 0
    times = 0
    avg_n = avg(list_s)
    for i in list_s:
        ret += (i - avg_n)**2
        times += 1
    ret = ret/times
    return math.sqrt(ret)

def write_right(tag_col, tag_n, ws):
    tag_list = [i.value for i in ws[tag_col]]
    row = tag_list.index(tag_n)
    # print(row+1)
    return row + 1

def inspection(endpoints, metric, cycle, sheet, tags=[]):
    stime = int(etime - cycle*24*3600)
    data = {'endpoints':endpoints, 'metric':metric, 'start':stime, 'end': etime, 'tags':tags}
    data = json.dumps(data)
    ret = requests.post("https://nightingale.smyoa.com/api/transfer/data/ui", headers=headers, data=data)
    infos = json.loads(ret.text)['dat']
    index = 0

    wb = load_workbook('d:/py文件/py脚本/test.xlsx')
    ws = wb[sheet]


    for info in infos:
        infos[index] = {key:info[key] for key in info.keys() if key == 'endpoint' or key == 'values'}
        infos[index]['values'] = [i['value'] for i in info['values'] if i['value']]
        if metric == 'mem.bytes.used.percent':
            # infos[index]['values'] = "/".join([str(round(max(infos[index]['values']),2)), str(round(min(infos[index]['values']),2)), str(round(avg(infos[index]['values']),2))]) if infos[index]['values'] else ""
            infos[index]['values'] = str(round(max(infos[index]['values']),2)) if infos[index]['values'] else ""
            # infos[index]['values'] = str(round(infos[index]['values'][0],2)) if infos[index]['values'] else ""
            
            ws.cell(write_right('C', infos[index]['endpoint'], ws), 4).value = infos[index]['values']
        elif metric == 'disk.bytes.used.percent':
            # infos[index]['values'] = "/".join([str(round(max(infos[index]['values']),2)), str(round(min(infos[index]['values']),2)), str(round(avg(infos[index]['values']),2))]) if infos[index]['values'] else ""
            infos[index]['values'] = str(round(infos[index]['values'][0],2)) if infos[index]['values'] else ""
           
            ws.cell(write_right('C', infos[index]['endpoint'], ws), 5).value = infos[index]['values']
        elif metric == 'cpu.loadavg.15':
            # infos[index]['values'] = "/".join([str(round(max(infos[index]['values']),2)), str(round(min(infos[index]['values']),2)), str(round(avg(infos[index]['values']),2))]) if infos[index]['values'] else ""
            infos[index]['values'] = "/".join([str(round(max(infos[index]['values']),2)), str(round(avg(infos[index]['values']),2))]) if infos[index]['values'] else ""
           
            ws.cell(write_right('C', infos[index]['endpoint'], ws), 6).value = infos[index]['values']
        elif metric == 'net.out.bits.total':
            # infos[index]['values'] = "/".join([str(round(max(infos[index]['values'])/1000000,2))+'M', str(round(min(infos[index]['values'])/1000000,2))+'M', str(round(avg(infos[index]['values'])/1000000,2))+'M']) if infos[index]['values'] else ""
            infos[index]['values'] = "/".join([str(round(max(infos[index]['values'])/1000000,2))+'M', str(round(avg(infos[index]['values'])/1000000,2))+'M', str(round(variance(infos[index]['values'])/1000000,2))]) if infos[index]['values'] else ""
           
            ws.cell(write_right('C', infos[index]['endpoint'], ws), 8).value = infos[index]['values']
        elif metric == 'net.in.bits.total':
            # infos[index]['values'] = "/".join([str(round(max(infos[index]['values'])/1000000,2))+'M', str(round(min(infos[index]['values'])/1000000,2))+'M', str(round(avg(infos[index]['values'])/1000000,2))+'M']) if infos[index]['values'] else ""
            infos[index]['values'] = "/".join([str(round(max(infos[index]['values'])/1000000,2))+'M', str(round(avg(infos[index]['values'])/1000000,2))+'M', str(round(variance(infos[index]['values'])/1000000,2))]) if infos[index]['values'] else ""
           
            ws.cell(write_right('C', infos[index]['endpoint'], ws), 7).value = infos[index]['values']
        elif metric == 'net.sockets.tcp.inuse':
            infos[index]['values'] = "/".join([str(round(max(infos[index]['values']),2)), str(round(min(infos[index]['values']),2)), str(round(avg(infos[index]['values']),2))]) if infos[index]['values'] else ""
            ws.cell(write_right('C', infos[index]['endpoint'], ws), 9).value = infos[index]['values']
        elif metric == 'net.sockets.tcp.timewait':
            infos[index]['values'] = "/".join([str(round(max(infos[index]['values']),2)), str(round(min(infos[index]['values']),2)), str(round(avg(infos[index]['values']),2))]) if infos[index]['values'] else ""
            ws.cell(write_right('C', infos[index]['endpoint'], ws), 10).value = infos[index]['values']

        index += 1
    wb.save('d:/py文件/py脚本/test.xlsx')
    return infos

# 1、2是日常巡检

ip_list1 = [
    '172.18.32.2', '172.18.32.3', '172.18.32.4', '172.18.32.5', '172.18.32.6', '172.18.32.7',
    '172.18.16.109', '172.18.16.111', '172.18.16.16', '172.18.16.17', '172.18.16.58',
    '172.18.16.12', '172.18.16.13', '172.18.16.31', '172.18.16.32', '172.18.16.163', '172.18.16.167',
    '172.18.16.23', '172.18.16.24', '172.18.16.159', '172.18.16.3',
    '172.18.16.126', '172.18.16.127', '172.18.16.155',
    '172.18.16.104', '172.18.16.145', '172.18.16.153', '172.18.16.173',   
]
metrics = ['cpu.loadavg.15', 'mem.bytes.used.percent', 'disk.bytes.used.percent', 'net.out.bits.total', 'net.in.bits.total']
for metric in metrics:
    if metric == 'disk.bytes.used.percent':
        inspection(ip_list1, metric, 1/24/60/3, "Sheet3", tags=["mount=/data"])
    else:
        inspection(ip_list1, metric, 1, "Sheet3")


ip_list2 = [
    '172.18.16.135', '172.18.16.136', '172.18.16.137'
]
for metric in metrics:
    if metric == 'disk.bytes.used.percent':
        inspection(ip_list2, metric, 1/24/60/3, "Sheet3", tags=["mount=/services"])
    else:
        inspection(ip_list2, metric, 1, "Sheet3")


# 3、4、5是周报
ip_list3 = ['172.18.16.14', '172.18.16.20', '172.18.16.21',
            '172.18.16.15', '172.18.16.33', '172.18.16.34',
            '172.18.16.119', '172.18.16.120',
            '172.18.16.145', '172.18.16.153', '172.18.16.173', '172.18.16.104',
]
metrics2 = ['cpu.loadavg.15', 'mem.bytes.used.percent', 'disk.bytes.used.percent', 'net.out.bits.total',
            'net.in.bits.total','net.sockets.tcp.inuse', 'net.sockets.tcp.timewait']
for metric in metrics2:
    if metric == 'disk.bytes.used.percent':
        inspection(ip_list3, metric, 7, "Sheet1", tags=["mount=/data"])
    else:
        inspection(ip_list3, metric, 7, "Sheet1")

ip_list4 = ['172.18.16.135', '172.18.16.136', '172.18.16.137']
for metric in metrics2:
    if metric == 'disk.bytes.used.percent':
        inspection(ip_list4, metric, 7, "Sheet1", tags=["mount=/services"])
    else:
        inspection(ip_list4, metric, 7, "Sheet1")

ip_list5 = ['172.18.82.1', '172.18.82.2', '172.18.82.3', '172.18.82.4', '172.18.82.5', '172.18.82.6',
            '172.18.36.1', '172.18.36.2', '172.18.36.3', '172.18.36.4',
            '172.18.56.12', '172.18.56.13', '172.18.56.14', '172.18.56.15', '172.18.56.16', '172.18.56.17',
            '172.18.56.18', '172.18.56.19', '172.18.56.30', '172.18.56.31', '172.18.56.32', '172.18.56.22',
            '172.18.56.23', '172.18.56.24', '172.18.56.25', '172.18.56.26', '172.18.56.27', '172.18.56.28',
            '172.18.56.29', '172.18.56.40', '172.18.56.41', '172.18.56.42'
]
metrics3 = ['cpu.loadavg.15', 'mem.bytes.used.percent', 'disk.bytes.used.percent', 'net.out.bits.total', 'net.in.bits.total']
for metric in metrics3:
    if metric == 'disk.bytes.used.percent':
        inspection(ip_list5, metric, 7, "Sheet2", tags=["mount=/pitrix"])
    else:
        inspection(ip_list5, metric, 7, "Sheet2")


